package cn.doitedu.visualiz.platform.service;

import cn.doitedu.visualiz.platform.dao.FunnelAnalysisDao;
import cn.doitedu.visualiz.platform.util.SqlHelper;
import cn.doitedu.visualiz.platform.vo.FunnelParams;
import cn.doitedu.visualiz.platform.vo.FunnelStep;
import cn.doitedu.visualiz.platform.vo.PropertyParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;


/**
with tmp as(
SELECT
guid,
concat_ws(',',array_agg(concat_ws(':',cast(ts as varchar),eventid))) as elst

FROM hive.test.action_dtl_ld
WHERE
(eventid='e1' and element_at(properties,'p1')='v1')
or
(eventid='e3' and element_at(properties,'p2')='v2')
or
(eventid='e5' and element_at(properties,'pt')='vr')

GROUP BY guid
)
select
guid,
case
  when regexp_extract(elst,'.*?(e1).*?(e3).*?(e5).*?',3)='e5' then 3
  when regexp_extract(elst,'.*?(e1).*?(e3).*?',2)='e3' then 2
  when regexp_extract(elst,'.*?(e1).*?',1)='e1' then 1
  else 0
end as step
from tmp
 * ;
 */

@Service
public class FunnelAnalysisServiceImpl implements FunnelAnalysisService {

    @Autowired
    FunnelAnalysisDao funnelAnalysisDao;


    @Override
    public HashMap<String, String> funnelAnalysis(FunnelParams funnelParams) {

        // 根据漏斗定义的条件，拼接sql

        String part1 = "with tmp as(\n" +
                "SELECT\n" +
                "guid,\n" +
                "concat_ws(',',array_agg(concat_ws(':',cast(ts as varchar),eventid))) as elst\n" +
                "\n" +
                "FROM hive.test.action_dtl_ld\n" +
                "WHERE ";


        // (eventid='e1' and element_at(properties,'p1')='v1')

        StringBuilder s1Builder = new StringBuilder();
        StringBuilder s2Builder = new StringBuilder();
        for (FunnelStep step : funnelParams.getSteps()) {

            s1Builder.append("(eventid='"+step.getEventId() + "' ");
            if(step.getPropertyParams().size()>0){
                s1Builder.append("and");
                StringBuilder eBuilder = new StringBuilder();
                for (PropertyParam propertyParam : step.getPropertyParams()) {
                    // element_at(properties,'p1')='v1')
                    eBuilder.append(" element_at(properties,'"+propertyParam.getPropertyName()+"')"+propertyParam.getCompareOperator()+"'"+propertyParam.getCompareValue()+"' and" );
                }

                s1Builder.append(eBuilder.toString().substring(0,eBuilder.length()-3));
            }

            s1Builder.append(") or");

        }

        String whereElement = s1Builder.toString().substring(0, s1Builder.length() - 2);

        String tmp = part1 + whereElement +"GROUP BY guid \n )" ;
        System.out.println(tmp);



        String finalSql = "with tmp as(\n" +
                "SELECT\n" +
                "guid,\n" +
                "concat_ws(',',array_agg(concat_ws(':',cast(ts as varchar),eventid))) as elst\n" +
                "\n" +
                "FROM hive.test.action_dtl_ld\n" +
                "WHERE \n" +
                "(eventid='e1' and element_at(properties,'p1')='v1') \n" +
                "or    \n" +
                "(eventid='e3' and element_at(properties,'p2')='v2')\n" +
                "or    \n" +
                "(eventid='e5' and element_at(properties,'pt')='vr')\n" +
                "\n" +
                "GROUP BY guid\n" +
                ")\n" +
                "select\n" +
                "guid,\n" +
                "case \n" +
                "  when regexp_extract(elst,'.*?(e1).*?(e3).*?(e5).*?',3)='e5' then 3\n" +
                "  when regexp_extract(elst,'.*?(e1).*?(e3).*?',2)='e3' then 2\n" +
                "  when regexp_extract(elst,'.*?(e1).*?',1)='e1' then 1\n" +
                "  else 0 \n" +
                "end as step\n" +
                "from tmp\n" ;

        HashMap<String, String> mp = new HashMap<>();

        try {
            mp = funnelAnalysisDao.queryUserCompleteStep(finalSql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return mp;
    }


    public static void main(String[] args) {

        FunnelAnalysisServiceImpl s = new FunnelAnalysisServiceImpl();

        FunnelParams funnelParams = new FunnelParams();
        funnelParams.setFunnelName("xxx");
        funnelParams.setTimeWindow(7);


        ArrayList<FunnelStep> steps = new ArrayList<>();
        FunnelStep step1 = new FunnelStep();

        step1.setEventId("e1");

        ArrayList<PropertyParam> propertyParams = new ArrayList<>();
        PropertyParam propertyParam = new PropertyParam();
        propertyParam.setPropertyName("p1");
        propertyParam.setCompareOperator("=");
        propertyParam.setCompareValue("v1");
        propertyParams.add(propertyParam);
        step1.setPropertyParams(propertyParams);

        steps.add(step1);

        funnelParams.setSteps(steps);

        s.funnelAnalysis(funnelParams);

    }
}
